package com.tsing.common.utils;

import com.tsing.common.exceptions.BusinessException;
import com.tsing.common.exceptions.CommonException;

import java.sql.*;
import java.util.ArrayList;

public class MysqlCollect {

    Connection conn = null;

    //获取conn
    public synchronized void init(String ip, String port, String DBResource, String username, String password) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
            conn = java.sql.DriverManager.getConnection("jdbc:mysql://" + ip + ":" + port + "/" + DBResource + "?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true", username, password);
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public String selectList(String sql) {
        StringBuffer jsonData = new StringBuffer("[");
        try {
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            ArrayList<String> columnNames = new ArrayList<>();
            for (int a = 1; a < columnCount + 1; a++) {
                String columnLabel = metaData.getColumnLabel(a);
                columnNames.add(columnLabel);
            }
            int columnNum = columnNames.size();
            while (resultSet.next()) {
                for (int i = 0; i < columnNum; i++) {
                    String columnName = columnNames.get(i);
                    Object object = resultSet.getObject(columnName);
                    if (columnNum == 1) {
                        jsonData.append("{\"" + columnName + "\"" + ":" + "\"" + object.toString() + "\"" + "},");
                    } else if (i == 0) {
                        jsonData.append("{\"" + columnName + "\"" + ":" + "\"" + object.toString() + "\"" + ",");
                    } else if (i == columnNum - 1) {
                        jsonData.append("\"" + columnName + "\"" + ":" + "\"" + object.toString() + "\"" + "},");
                    }
                }
            }
            int len = jsonData.length();
            jsonData.replace(len - 1, len, "]");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return jsonData.toString();
    }

    public String selectOne(String sql) {
        StringBuffer jsonData = new StringBuffer();
        try {
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            ArrayList<String> columnNames = new ArrayList<>();
            for (int a = 1; a < columnCount + 1; a++) {
                String columnLabel = metaData.getColumnLabel(a);
                columnNames.add(columnLabel);
            }
            int columnNum = columnNames.size();
            int count = 0;
            while (resultSet.next()) {
                if (count++ != 0) {
                    throw new BusinessException(CommonException.Proxy.SELECT_COUNT_NOT_IS_ONE);
                }
                for (int i = 0; i < columnNum; i++) {
                    String columnName = columnNames.get(i);
                    Object object = resultSet.getObject(columnName);
                    if (columnNum == 1) {
                        jsonData.append("{\"" + columnName + "\"" + ":" + "\"" + object.toString() + "\"" + "}");
                    } else if (i == 0) {
                        jsonData.append("{\"" + columnName + "\"" + ":" + "\"" + object.toString() + "\"" + ",");
                    } else if (i == columnNum - 1) {
                        jsonData.append("\"" + columnName + "\"" + ":" + "\"" + object.toString() + "\"" + "}");
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return jsonData.toString();
    }

    public String selectValue(String sql) {
        String result = null;
        try {
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            ArrayList<String> columnNames = new ArrayList<>();
            for (int a = 1; a < columnCount + 1; a++) {
                String columnLabel = metaData.getColumnLabel(a);
                columnNames.add(columnLabel);
            }
            int columnNum = columnNames.size();
            if (columnNum > 1) {
                throw new BusinessException(CommonException.Proxy.SELECT_COUNT_NOT_IS_ONE);
            }
            int count = 0;
            while (resultSet.next()) {
                if (count++ != 0) {
                    throw new BusinessException(CommonException.Proxy.SELECT_COUNT_NOT_IS_ONE);
                }
                String columnLabel = metaData.getColumnLabel(1);
                Object object = resultSet.getObject(columnLabel);
                result = object.toString();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    //释放conn
    public synchronized void destroy() {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}
